﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;//xls,Excel97~2003格式
using NPOI.XSSF.UserModel;//xlsx,Excel2007格式
using NPOI.SS.UserModel;
using NPOI.SS.Util;

// ReSharper disable once CheckNamespace
namespace Tzx.Utilities.Npoi
{
    /// <summary>
    /// Npoi静态实用帮助方法
    /// </summary>
    public partial class NpoiUtilities
    {
        /// <summary>
        /// 样式
        /// </summary>
        public static bool GetStyle(ISheet sheet, int rowIndex, int startColumnIndex, int endColumnIndex,
            out ICellStyle rowStyle, out Dictionary<int, ICellStyle> cellStyles)
        {
            //表不存在
            if (sheet == null || sheet.Workbook == null)
            {
                rowStyle = null;
                cellStyles = null;
                return false;
            }

            cellStyles = new Dictionary<int, ICellStyle>();

            //行不存在
            IRow excelRow = sheet.GetRow(rowIndex);
            if (excelRow == null)
            {
                rowStyle = null;
                cellStyles = null;
                return false;
            }

            //行(存在行)
            rowStyle = excelRow.RowStyle;

            //
            for (int col = startColumnIndex; col <= endColumnIndex; col++)
            {
                ICell excelCell = excelRow.GetCell(col);
                if (excelCell == null)
                {
                    cellStyles.Add(col, null);
                    continue;
                }

                //
                cellStyles.Add(col, excelCell.CellStyle);
            } //for

            return true;
        }

        /// <summary>
        /// 保存Excel流到到本机硬盘文件(注意Excel2007的流关闭问题)
        /// </summary>
        /// <param name="filePathName">保存文件名</param>
        /// <param name="ms"></param>
        public static void ExportToWin(string filePathName, MemoryStream ms)
        {
            //BUG:Wrong Local header signature:0xE011CFD0。
            if (ms == null)
            {
                return;
            }

            //Excel2007格式,会出现"发现xxx中部分内容有问题",改为stream.ToArray()

            #region 不适用Excel2007
            //workbook.Write(stream);
            //stream.GetBuffer();//Excel2007格式,会出现"发现xxx中部分内容有问题",改为stream.ToArray()
            //stream.ToArray();
            //FileStream fs = new FileStream(filePathName, FileMode.OpenOrCreate);
            //BinaryWriter bw = new BinaryWriter(fs);
            //byte[] bb = new byte[stream.Length];
            //stream.Read(bb, 0, bb.Length);
            //stream.Seek(0, SeekOrigin.Begin);
            //bw.Write(bb); //WinForm
            //fs.Close();
            //stream.Close();
            #endregion

            //
            byte[] bbOk = ms.ToArray();
            FileStream fs = new FileStream(filePathName, FileMode.OpenOrCreate);
            fs.Write(bbOk, 0, bbOk.Length);
            fs.Dispose();
            ms.Close();
        }

        /// <summary>
        /// Excel文件内容保存为文件(已处理流关闭问题OK)
        /// </summary>
        /// <param name="filePathName"></param>
        /// <param name="book"></param>
        public static void ExportToWin(string filePathName, IWorkbook book)
        {
            if (book == null)
            {
                return;
            }

            //
            NpoiMemoryStream ms = new NpoiMemoryStream();
            ms.AllowClose = false; //防关闭
            book.Write(ms);
            ms.Flush();
            ms.Position = 0;
            book = null;
            ms.AllowClose = true; //防关闭

            //
            byte[] bbOk = ms.ToArray();
            FileStream fs = new FileStream(filePathName, FileMode.OpenOrCreate);
            fs.Write(bbOk, 0, bbOk.Length);
            fs.Dispose();
            ms.Close();
        }

        /// <summary>
        /// Excel文件内容导出为二进制数组(已处理流关闭问题)
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public static byte[] Export(IWorkbook book)
        {
            if (book == null)
            {
                return null;
            }

            //
            NpoiMemoryStream ms = new NpoiMemoryStream();
            ms.AllowClose = false; //防关闭
            book.Write(ms);
            ms.Flush();
            ms.Position = 0;
            book = null;
            ms.AllowClose = true; //防关闭

            //
            byte[] bbOk = ms.ToArray();
            ms.Close();

            //返回
            return bbOk;
        }

        /// <summary>
        /// 导出参数:设置替换DataTable标题Caption
        /// </summary>
        /// <param name="table">原表格</param>
        /// <param name="colNameSs">列名</param>
        /// <param name="captionSs">新列标题</param>
        /// <returns></returns>
        public static bool SetDataTableCaption(DataTable table, string[] colNameSs, string[] captionSs)
        {
            if (table == null)
            {
                return false;
            }
            bool ok =
                colNameSs != null &&
                colNameSs.Length > 0 &&
                captionSs != null &&
                captionSs.Length > 0 &&
                captionSs.Length == colNameSs.Length;
            if (!ok)
            {
                return false;
            }

            //
            for (int i = 0; i < colNameSs.Length; i++)
            {
                string colName = colNameSs[i];
                string colCaption = captionSs[i];
                if (table.Columns.Contains(colName))
                {
                    table.Columns[colName].Caption = colCaption;
                }
            }

            return true;
        }

        /// <summary>
        /// 导出参数:排序列(DataTable列顺序)
        /// </summary>
        public static bool SetDataTableSort(DataTable table, string[] colNameSs)
        {
            if (table == null)
            {
                return false;
            }

            if (colNameSs == null || colNameSs.Length == 0)
            {
                return false;
            }

            //
            int max = table.Columns.Count;
            int colIndex = 0;
            for (int i = 0; i < colNameSs.Length; i++)
            {
                string colName = colNameSs[i];
                if (table.Columns.Contains(colName))
                {
                    table.Columns[colName].SetOrdinal(colIndex);
                    colIndex += 1;
                }
                if (colIndex > max)
                {
                    break;
                }
            }

            return true;
        }

        #region 导入
        
        /// <summary>
        /// 导入(Excel流=>DataTable,全部Index参数从0开始)
        /// 2022-06-27,判断文件扩展名是xls还是xlsx:
        /// xls  => HSSFWorkbook
        /// xlsx => XSSFWorkbook
        /// </summary>
        /// <param name="excelFileStream">文件流</param>
        /// <param name="sheetIndex">工作表索引(从0开始)</param>
        /// <param name="headerRowIndex">从第几行开始(若第一行为标题要从第二行开始,则传入1)</param>
        /// <param name="isExcel2007">是否为新2007文件格式,默认为为97~2003格式</param>
        /// <returns></returns>
        public static DataTable ExcelStreamToDataTable(Stream excelFileStream, int sheetIndex, int headerRowIndex,bool isExcel2007)
        {
            //XSSFWorkbook workbook = new XSSFWorkbook(excelFileStream);
            IWorkbook workbook;
            if (!isExcel2007)
            {
                workbook = new HSSFWorkbook(excelFileStream);//Excel97-2003
            }
            else
            {
                workbook = new XSSFWorkbook(excelFileStream); //Excel2007
            }

            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            //HSSFSheet templateExcelSheet = templateExcel.GetSheetAt(sheetIndex);

            DataTable table = new DataTable();
            table.TableName = "Import" + DateTime.Now.ToString("yyyyMMddHHmmss");

            IRow headerRow = sheet.GetRow(headerRowIndex);
            //HSSFRow headerRow = templateExcelSheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum; //列数,BUG:未将对象引用设置到对象的实例。
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                //DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);//避免数字标题
                if (headerRow.GetCell(i) == null)
                {
                    cellCount--;
                    continue;
                }
                string text = headerRow.GetCell(i).ToString().Trim(' ', '_');
                DataColumn column = new DataColumn(text);
                column.Caption = text; //使用DataTable的标题
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum; //不包含标题的行数

            //for (int i = (templateExcelSheet.FirstRowNum + 1); i < templateExcelSheet.LastRowNum; i++)
            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum + 1; i++)
            {
                IRow row = sheet.GetRow(i); //HSSFRow row = templateExcelSheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        //dataRow[j] = row.GetCell(j).ToString();
                        ICell cell = row.GetCell(j); //HSSFCell cell = row.GetCell(j);

                        //日期型数值
                        //if (cell.CellType == HSSFCell.CELL_TYPE_NUMERIC && HSSFDateUtil.IsCellDateFormatted(cell))
                        //if (cell.CellType == CellType.NUMERIC && HSSFDateUtil.IsCellDateFormatted(cell))
                        if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                        {
                            dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        else
                        {
                            dataRow[j] = cell.ToString();
                        }
                    }
                }

                table.Rows.Add(dataRow);
            }

            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 从Excel文件导入生成新的DataTable
        /// </summary>
        /// <param name="excelFileName"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="headIndex"></param>
        /// <returns></returns>
        public static DataTable ImportFromFile(string excelFileName, int sheetIndex, int headIndex)
        {
            //判断是xls还是xlsx
            bool isExcel2007;
            string s = string.Format("{0}", excelFileName).Trim().ToLower();
            string ext = s.Substring(s.LastIndexOf('.')).Trim('.');
            if (String.Compare(ext, "xlsx", StringComparison.OrdinalIgnoreCase) == 0)
            {
                isExcel2007 = true;//excel 2007 格式
            }
            else if (String.Compare(ext, "xls", StringComparison.OrdinalIgnoreCase) == 0)
            {
                isExcel2007 = false;//excel 97~2003 格式
            }
            else
            {
                //错误
                DataTable tableErr = new DataTable();
                tableErr.Columns.Add("导入错误", typeof(string));
                DataRow row = tableErr.NewRow();
                row[0] = "Excel文件扩展名错误(xls或xlsx)";
                tableErr.Rows.Add(row);
                return tableErr;
            }

            //开始导入
            FileStream fs = new FileStream(excelFileName, FileMode.Open, FileAccess.Read);
            DataTable table2 = ExcelStreamToDataTable(fs, sheetIndex, headIndex, isExcel2007);
            fs.Close();
            return table2;
        }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="worksheet"></param>
        /// <param name="sourceRowNum"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        private static IRow InsertRows(ref ISheet worksheet, int sourceRowNum, int rowCount)
        {
            worksheet.ShiftRows(sourceRowNum, worksheet.LastRowNum, rowCount, true, false);
            //worksheet.ShiftRows(sourceRowNum, worksheet.LastRowNum,1, true, false);

            IRow rowFirst = null;
            for (int rowIndex = sourceRowNum; rowIndex < sourceRowNum + rowCount; rowIndex++)
            {
                IRow rowSource = worksheet.GetRow(rowIndex + rowCount);
                IRow rowInsert = worksheet.CreateRow(rowIndex);
                rowInsert.Height = rowSource.Height;

                //
                if (rowFirst == null)
                {
                    rowFirst = rowInsert;
                }

                //
                for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                {
                    ICell cellSource = rowSource.GetCell(colIndex);
                    ICell cellInsert = rowInsert.CreateCell(colIndex);
                    if (cellSource != null)
                    {
                        cellInsert.CellStyle = cellSource.CellStyle;

                        //如果是公式
                        if (cellSource.CellType == CellType.Formula)
                        {
                            cellInsert.SetCellType(CellType.Formula);
                            cellInsert.SetCellFormula(cellSource.CellFormula);
                        }
                    }
                }//for
            }//for

            return rowFirst;
        }

        /// <summary>
        /// 复制行，可用于按模板文件导出
        /// 使用示例:
        /// HSSFWorkbook workbook = new HSSFWorkbook(memoryStream);
        /// HSSFSheet wooksheet = workbook.GetSheet("Sheet1");
        /// CopyRow(wooksheet , 0, 2);
        /// CopyRow(wooksheet , 1, 3);
        /// 代码作者网址: http://www.zachhunter.com/2010/05/npoi-copy-row-helper/
        /// </summary>
        /// <remarks>
        /// Inserts a existing row into a new row, will automatically push down
        /// any existing rows.  Copy is done cell by cell and supports, and the
        /// command tries to copy all properties available (style, merged cells, values, etc...)
        /// </remarks>
        /// <param name="worksheet">WorkSheet containing rows to be copied</param>
        /// <param name="sourceRowNum">Source Row Number</param>
        /// <param name="destinationRowNum">Destination Row Number</param>
        public static void CopyRow(ISheet worksheet, int sourceRowNum, int destinationRowNum)
        {
            //Workbook containing the worksheet that will be changed
            //IWorkbook workbook, 

            // Get the source / new row
            IRow newRow = worksheet.GetRow(destinationRowNum);
            IRow sourceRow = worksheet.GetRow(sourceRowNum);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
            }
            else
            {
                newRow = worksheet.CreateRow(destinationRowNum);
            }

            // Loop through source columns to add to new row
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = newRow.CreateCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }

                // Copy style from old cell and apply to new cell
                //ICellStyle newCellStyle = workbook.CreateCellStyle();
                ICellStyle newCellStyle = worksheet.Workbook.CreateCellStyle();
                newCellStyle.CloneStyleFrom(oldCell.CellStyle);
                newCell.CellStyle = newCellStyle;

                // If there is a cell comment, copy
                if (newCell.CellComment != null)
                {
                    newCell.CellComment = oldCell.CellComment;
                }

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null)
                {
                    newCell.Hyperlink = oldCell.Hyperlink;
                }

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.Blank://case HSSFCellType.BLANK:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.Boolean://case HSSFCellType.BOOLEAN:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.Error://case HSSFCellType.ERROR:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.Formula://case HSSFCellType.FORMULA:
                        //公式
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.Numeric://case HSSFCellType.NUMERIC:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.String://case HSSFCellType.STRING:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown://case HSSFCellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }//for

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress == null) //未将对象引入到实例,暂时这么解决
                {
                    continue;
                }
                if (cellRangeAddress.FirstRow == sourceRow.RowNum) //BUG:Excel2007出错
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                        (newRow.RowNum +
                         (cellRangeAddress.FirstRow -
                          cellRangeAddress.LastRow)),
                        cellRangeAddress.FirstColumn,
                        cellRangeAddress.LastColumn);
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            } //for

        }

    }

    #region 代码备份
    ////模板行在前
    //[Obsolete("代码备份")]
    //public static IRow CopyRow2(ISheet worksheet, int sourceRowNum, int rowNewNum)
    //{
    //    IRow rowNew = worksheet.GetRow(rowNewNum);//目的
    //    if (rowNew != null)
    //    {
    //        //如果行存在则插入一个新行
    //        worksheet.ShiftRows(rowNewNum, worksheet.LastRowNum, 1, true, false);//模板行在前
    //    }
    //    else
    //    {
    //        throw new ApplicationException("这个可以不要");
    //        rowNew = worksheet.CreateRow(rowNewNum);
    //    }

    //    IRow rowSource = worksheet.GetRow(sourceRowNum);//来源
    //    for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
    //    {
    //        ICell cellSource = rowSource.GetCell(colIndex);
    //        ICell cellNew = rowNew.CreateCell(colIndex);

    //        //单元格为空则跳过
    //        if (cellSource == null)
    //        {
    //            cellNew = null;
    //            continue;
    //        }

    //        //从旧单元格复制样式,到新单元格
    //        //ICellStyle newCellStyle = worksheet.Workbook.CreateCellStyle();
    //        //newCellStyle.CloneStyleFrom(cellSource.CellStyle);
    //        //cellNew.CellStyle = newCellStyle;

    //        //样式一样
    //        cellNew.CellStyle = cellSource.CellStyle;

    //        //批注
    //        //if (cellNew.CellComment != null)
    //        //{
    //        //    cellNew.CellComment = cellSource.CellComment;
    //        //}

    //        //超链接
    //        //if (cellSource.Hyperlink != null)
    //        //{
    //        //    cellNew.Hyperlink = cellSource.Hyperlink;
    //        //}

    //        //设置单元格数据类型
    //        //cellNew.SetCellType(cellSource.CellType);

    //        //赋值
    //        switch (cellSource.CellType)
    //        {
    //            case CellType.FORMULA:
    //                //如果是公式
    //                cellNew.SetCellType(cellSource.CellType);//设置单元格数据类型
    //                cellNew.SetCellFormula(cellSource.CellFormula);
    //                break;
    //            default:
    //                //cellNew.SetCellValue(string.Empty);
    //                break;
    //        }//switch

    //    }//for

    //    //原行中存在合并单元格
    //    for (int i = 0; i < worksheet.NumMergedRegions; i++)
    //    {
    //        CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
    //        if (cellRangeAddress.FirstRow == rowSource.RowNum)
    //        {
    //            CellRangeAddress newCellRangeAddress = new CellRangeAddress(rowNew.RowNum,
    //                (rowNew.RowNum +
    //                 (cellRangeAddress.FirstRow -
    //                  cellRangeAddress.LastRow)),
    //                cellRangeAddress.FirstColumn,
    //                cellRangeAddress.LastColumn);
    //            worksheet.AddMergedRegion(newCellRangeAddress);
    //        }
    //    }//for

    //    //返回
    //    return rowNew;
    //}

    ////模板行在后
    //[Obsolete("代码备份")]
    //public static IRow InsertRows2(ISheet worksheet, int sourceRowNum)
    //{
    //    worksheet.ShiftRows(sourceRowNum + 1, worksheet.LastRowNum, 1, true, false);//模板行在后
    //    IRow rowSource = worksheet.GetRow(sourceRowNum );
    //    IRow rowNew = worksheet.CreateRow(sourceRowNum+ 1);
    //    rowNew.Height = rowSource.Height;



    //    for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
    //    {
    //        ICell cellSource = rowSource.GetCell(colIndex);
    //        ICell cellNew = rowNew.CreateCell(colIndex);

    //        //单元格为空则跳过
    //        if (cellSource == null)
    //        {
    //            cellNew = null;
    //            continue;
    //        }

    //        //样式一样
    //        cellNew.CellStyle = cellSource.CellStyle;

    //        //设置单元格数据类型
    //        //cellNew.SetCellType(cellSource.CellType);

    //        //赋值
    //        switch (cellSource.CellType)
    //        {
    //            case CellType.FORMULA:
    //                //如果是公式
    //                cellNew.SetCellType(cellSource.CellType); //设置单元格数据类型
    //                cellNew.SetCellFormula(cellSource.CellFormula);
    //                break;
    //            default:
    //                //cellNew.SetCellValue(string.Empty);
    //                break;
    //        }//switch

    //    } //for



    //    return rowNew;
    //}
    #endregion

    /// <summary>
    /// 
    /// </summary>
    public partial class NpoiUtilities
    {
        private static IRow InsertCopyRow_1(ISheet worksheet, int sourceRowNum, int rowNewNum)
        {
            IRow rowSource = worksheet.GetRow(sourceRowNum);//来源
            IRow rowNew = worksheet.GetRow(rowNewNum);//目的
            worksheet.ShiftRows(rowNewNum, worksheet.LastRowNum, 1, true, false);//模板行在前

            for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
            {
                ICell cellSource = rowSource.GetCell(colIndex);
                ICell cellNew = rowNew.CreateCell(colIndex);

                //单元格为空则跳过
                if (cellSource == null)
                {
                    cellNew = null;
                    continue;
                }

                //样式一样
                cellNew.CellStyle = cellSource.CellStyle;

                //批注
                if (cellNew.CellComment != null)
                {
                    cellNew.CellComment = cellSource.CellComment;
                }

                //超链接
                if (cellSource.Hyperlink != null)
                {
                    cellNew.Hyperlink = cellSource.Hyperlink;
                }

                //赋值
                switch (cellSource.CellType)
                {
                    case CellType.Formula:
                        //如果是公式
                        cellNew.SetCellType(cellSource.CellType);//设置单元格数据类型
                        cellNew.SetCellFormula(cellSource.CellFormula);
                        break;
                    default:
                        //cellNew.SetCellType(cellSource.CellType);
                        //cellNew.SetCellValue(string.Empty);
                        break;
                }//switch

            }//for

            //原行中存在合并单元格
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == rowSource.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(
                        rowNew.RowNum,
                        rowNew.RowNum + (cellRangeAddress.FirstRow - cellRangeAddress.LastRow),
                        cellRangeAddress.FirstColumn,
                        cellRangeAddress.LastColumn
                        );
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            }//for

            //返回
            return rowNew;
        }

        /// <summary>
        /// 指定位置插入
        /// </summary>
        public static IRow InsertCopyRow_2(ISheet worksheet, int sourceRowNum, int rowNewNum)
        {
            worksheet.ShiftRows(rowNewNum, worksheet.LastRowNum, 1, true, false);//模板行在前
            IRow rowSource = worksheet.GetRow(sourceRowNum);//来源
            IRow rowNew = worksheet.GetRow(rowNewNum);//目的

            for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
            {
                ICell cellSource = rowSource.GetCell(colIndex);
                ICell cellNew = rowNew.CreateCell(colIndex);

                //单元格为空则跳过
                if (cellSource == null)
                {
                    cellNew = null;
                    continue;
                }

                //样式一样
                cellNew.CellStyle = cellSource.CellStyle;

                //批注
                if (cellNew.CellComment != null)
                {
                    cellNew.CellComment = cellSource.CellComment;
                }

                //超链接
                if (cellSource.Hyperlink != null)
                {
                    cellNew.Hyperlink = cellSource.Hyperlink;
                }

                //赋值
                switch (cellSource.CellType)
                {
                    case CellType.Formula:
                        //如果是公式
                        cellNew.SetCellType(cellSource.CellType);//设置单元格数据类型
                        cellNew.SetCellFormula(cellSource.CellFormula);
                        break;
                    default:
                        //cellNew.SetCellType(cellSource.CellType);
                        //cellNew.SetCellValue(string.Empty);
                        break;
                }//switch

            }//for

            //原行中存在合并单元格
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == rowSource.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(
                        rowNew.RowNum,
                        rowNew.RowNum + (cellRangeAddress.FirstRow - cellRangeAddress.LastRow),
                        cellRangeAddress.FirstColumn,
                        cellRangeAddress.LastColumn
                        );
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            }//for

            //返回
            return rowNew;
        }

        /// <summary>
        /// 在前面插入
        /// </summary>
        public static IRow InsertCopyRow_3(ISheet worksheet, int sourceRowNum,int rowNewNum)
        {
            //worksheet.ShiftRows(sourceRowNum + 1, worksheet.LastRowNum, 1, true, false);//模板行在后
            worksheet.ShiftRows(sourceRowNum, worksheet.LastRowNum, 1, true, false);//在前方插入,模板行被推后
            IRow rowSource = worksheet.GetRow(sourceRowNum + 1);//原模板行
            //IRow rowNew = worksheet.CreateRow(sourceRowNum);//新行
            IRow rowNew = worksheet.CreateRow(rowNewNum);//新行
            rowNew.Height = rowSource.Height;

            for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
            {
                ICell cellSource = rowSource.GetCell(colIndex);
                ICell cellNew = rowNew.CreateCell(colIndex);

                //单元格为空则跳过
                if (cellSource == null)
                {
                    cellNew = null;
                    continue;
                }

                //样式一样
                cellNew.CellStyle = cellSource.CellStyle;

                //设置单元格数据类型
                //cellNew.SetCellType(cellSource.CellType);

                //赋值
                switch (cellSource.CellType)
                {
                    case CellType.Formula:
                        //如果是公式
                        cellNew.SetCellType(cellSource.CellType); //设置单元格数据类型
                        cellNew.SetCellFormula(cellSource.CellFormula);
                        break;
                    default:
                        //cellNew.SetCellValue(string.Empty);
                        break;
                }//switch

            } //for



            return rowNew;
        }
    }





    /// <summary>
    /// 从MemoryStream派生一个新类,专给NPOI用,解决XSSFWorkbook.Write方法后关闭流的问题
    /// <remarks>
    /// 导出Excel2007格式时(XSSFWorkbook),无法访问已关闭的流,而Excel97~2003格式(HSSFWorkbook)没问题.
    /// XSSFWorkbook,出现这个错误,是因为在使用book.Write方法后,会关闭流,这样导致再次使用Respons输出流的时候就出错了
    /// 解决这个问题,需要从MemoryStream派生一个新类,专给NPOI用,解决流关闭的问题.
    /// </remarks>
    /// </summary>
    public class NpoiMemoryStream : MemoryStream
    {
        /// <summary>
        /// 
        /// </summary>
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        /// <summary>
        /// 
        /// </summary>
        public bool AllowClose { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public override void Close()
        {
            if (AllowClose)
            {
                base.Close();
            }
        }
    }





}